/**
 * Copyright 2005 The Apache Software Foundation
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.edi.db;

import java.io.Reader;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.edi.db.dialect.Dialect;
import com.edi.db.table.ColumnCell;
import com.edi.db.table.FieldEntity;
import com.edi.db.table.RowEntity;
import com.edi.db.table.TableEntity;
import com.edi.db.type.NullableType;
import com.edi.util.StringHelper;

public class SessionImpl implements Session {
	private static final Log log = LogFactory.getLog(SessionImpl.class);

	private SessionFactory sessionFactory;

	private boolean openTransaction;

	private Connection connection;

	private Dialect dialect;

	public SessionImpl(Connection connection, Dialect dialect)
			throws SQLException {
		this.connection = connection;
		connection.setAutoCommit(true);
		this.dialect = dialect;
	}

	/**
	 * 关闭连接
	 */
	public void close() {
		if (this.connection != null)
			try {
				if (!this.connection.isClosed()) {
					this.connection.close();
					this.connection = null;
				}
			} catch (SQLException e) {
				log.error("关闭session 发生异常！" + e.getMessage());
				e.printStackTrace();
			}
	}

	/**
	 * 执行删除操作
	 * 
	 * @throws SQLException
	 */
	public int delete(TableEntity tableEntity) throws SQLException {
		PreparedStatement st = null;
		try {
			List list = tableEntity.getFieldNameList();
			st = connection.prepareStatement(dialect
					.getDeletePreparedString(tableEntity));
			List<String> keys = tableEntity.getPrimarykey();
			List<RowEntity> rows = tableEntity.getRows();
			for (RowEntity row : rows) {
				for (int i = 0; i < keys.size(); i++) {
					String keyField = keys.get(i);
					NullableType fieldType = tableEntity.getFieldType(keyField);
					if (fieldType != null) {
						fieldType.set(st, row.getValue(keyField), i + 1);
					}
				}
				st.addBatch();
			}
			st.executeBatch();
		} finally {
			if (st != null)
				st.close();
		}
		return 0;
	}

	public TableEntity queryEql(String eql) throws SQLException {
		return queryEql(eql, null, null);
	}

	/**
	 * 执行eql查询 返回TableEntity对象 start--起始行数 pageSize --每页记录数
	 */
	public TableEntity queryEql(String eql, Integer start, Integer pageSize)
			throws SQLException {
		// try {
		// 1.解析出eql 生成方言sql
		String sql = dialect.translate(eql);
		// 2.执行方言sql
		return this.querySql(sql, start, pageSize);
		// } catch (Exception e) {
		// e.printStackTrace();
		// }

	}

	/**
	 * 执行update，或者insert，或者delete
	 * 
	 * @param eql
	 * @return
	 */
	public int executeEql(String eql) throws SQLException {
		// 1.解析出eql 生成方言sql
		String sql = dialect.translate(eql);
		// 2.执行方言sql
		return this.excuteSql(sql);
	}

	public boolean hasTable(String tableName) throws SQLException {
		String sql = dialect.ifExitTable(tableName);
		return querySql(sql).getRows().size() != 0;
	}

	public void createBaseTable(TableEntity tableEntity) throws SQLException {
		String sql = dialect.createTable(tableEntity);
		log.debug("create table sql =" + sql);
		excuteSql(sql);
	}

	/**
	 * 创建一个表，会根据该表的具体情况，进行创建或修改
	 * 
	 * @param tableEntity
	 * @return
	 */
	public void createTable(TableEntity tableEntity) throws SQLException {

		if (tableEntity == null) {
			return;
		}
		String sql = dialect.ifExitTable(tableEntity.getTableName());
		TableEntity te = querySql(sql);
		List<FieldEntity> addList = new ArrayList<FieldEntity>();
		List<FieldEntity> delList = new ArrayList<FieldEntity>();
		List<FieldEntity> modList = new ArrayList<FieldEntity>();
		//		log.debug(" te.getRows().size() ="+te.getRows().size());
		if (te.getRows().size() == 0) {
			// 表不存在 新建表
			sql = dialect.createTable(tableEntity);
			excuteSql(sql);

		} else {
			String eql = "select * from " + tableEntity.getTableName()
					+ " where 1<>1";
			//			log.debug(" eql ="+eql);
			TableEntity dest = this.queryEql(eql);
			//			log.debug(" dest ="+dest.getRows().size());
			// 表存在，则需要对照表中的具体字段多减少增
			Hashtable<String, FieldEntity> sourceHash = tableEntity
					.getFieldEntitys();
			Hashtable<String, FieldEntity> destinHash = dest.getFieldEntitys();
			//			log.debug(" destinHash "+destinHash.size());
			Set<String> sourceSet = sourceHash.keySet();
			Iterator<String> it = sourceSet.iterator();
			//			log.debug("tableEntity  tableName ="+tableEntity.getTableName());
			while (it.hasNext()) {
				String fieldName = it.next();
				if (fieldName == null || "".equals(fieldName))
					continue;
				if (destinHash.get(fieldName) == null) {
					addList.add(sourceHash.get(fieldName));
				} else {
					FieldEntity desFieldEntity = destinHash.get(fieldName);
					//					log.debug("desFieldEntity ="+desFieldEntity.getFieldType().sqlType()+" // "+desFieldEntity.getFieldType().getName()+" // "+desFieldEntity.getFieldName());
					FieldEntity sourceFieldEntity = sourceHash.get(fieldName);
					//					log.debug("sourceFieldEntity ="+sourceFieldEntity.getFieldType().sqlType()+" // "+sourceFieldEntity.getFieldType().getName()+" // "+sourceFieldEntity.getFieldName());
					if (!desFieldEntity.equals(sourceFieldEntity, dialect)
							&& !desFieldEntity.isIdentify())
						modList.add(sourceFieldEntity);
					destinHash.remove(fieldName);
				}
			}
			Set delSet = destinHash.keySet();
			Iterator delIt = delSet.iterator();
			while (delIt.hasNext()) {
				String fieldName = (String) delIt.next();
				FieldEntity delFieldEntity = (FieldEntity) destinHash
						.get(fieldName);
				delList.add(delFieldEntity);
			}
			// 根据 addlist，dellist，modlist 对表列进行增删改操作
			String addSql = dialect.addColumn(tableEntity.getTableName(),
					addList);
			log.debug(" addSql= " + addSql);
			excuteSql(addSql);
			String delSql = dialect.dropColumn(tableEntity.getTableName(),
					delList);
			log.debug(" delSql= " + delSql);
			excuteSql(delSql);
			String updateSql = dialect.modifyColumn(tableEntity.getTableName(),
					modList);
			log.debug(" updateSql= " + updateSql);
			excuteSql(updateSql);
		}
	}

	public TableEntity querySql(String sql) throws SQLException {
		log.debug(" enter excuteSqlQuery no page " + sql);
		return querySql(sql, null, null);
	}

	/**
	 * 执行sql方言 返回TableEntity对象 pageIndex--当前页 pageSize --页面记录数
	 * 
	 * @throws SQLException
	 */
	public TableEntity querySql(String sql, Integer start, Integer pageSize)
			throws SQLException {
		log.debug(" enter excuteSqlQuery " + sql);
		boolean pagination = start != null && pageSize != null;
		int intStart = 0;
		int intPageSize = Integer.MAX_VALUE;
		if (pagination) {
			log.debug("before intStart= " + intStart);
			intStart = start;
			log.debug("after intStart= " + intStart);
			intPageSize = pageSize.intValue();
		}
		String tableName = analyzTableName(sql);
		List<String> primFields = new ArrayList<String>();
		TableEntity table = new TableEntity(tableName);
		Statement st = null;
		ResultSet rs = null;
		ResultSet dbSet = null;
		log.debug("pagination =" + pagination);
		try {
			DatabaseMetaData dbmeta = connection.getMetaData();
			// 获得主键信息
			if (!tableName.equals(EdiDb.TABLE_EDI_SYSTEMP)) {
				dbSet = dbmeta.getPrimaryKeys(null, null, tableName);
				while (dbSet.next()) {
					primFields
							.add(dbSet.getString("COLUMN_NAME").toLowerCase());
				}
			}
			log.debug(" intStart= " + intStart);
			log.debug(" intPageSize= " + intPageSize);
			log.debug(" intStart= " + intStart);
			if (pagination) {
				log.debug("开始设置statement分页属性");
				//st = connection.createStatement();
				st = connection.createStatement(
						//ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				//				st = connection.createStatement();
				st.setMaxRows(intStart + intPageSize);
			} else
				st = connection.createStatement();
			rs = st.executeQuery(sql);
			if (pagination)
				try {
					rs.absolute(intStart);
				} catch (Exception e) {
					e.printStackTrace();
					int index = 0;
					while (index < intStart) {
						if (rs.next())
							index++;
						else
							continue;
					}
				}
			ResultSetMetaData meta = rs.getMetaData();
			// 获取表中的字段信息
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				FieldEntity field = new FieldEntity();
				field.setFieldName(meta.getColumnName(i).toLowerCase());
				field.setFieldType(dialect.getSqlType(meta.getColumnType(i),
						meta.getPrecision(i), meta.getScale(i)));
				field.setSize(meta.getColumnDisplaySize(i));
				field.setScale(meta.getScale(i));
				field.setPrecision(meta.getPrecision(i));
				if (primFields.contains(field.getFieldName())) {
					field.setIdentify(true);
				}
				table.addField(field);
			}
			log.debug(" 2 ");
			// analyzeResultSet(table,connection,rs, sql);
			// 获取表中的行信息
			while (rs.next()) {
				Collection<FieldEntity> fields = table.getFieldEntitys()
						.values();
				log.debug("fields =" + fields.size());
				Iterator<FieldEntity> fieldsIter = fields.iterator();
				RowEntity row = table.newRow();
				while (fieldsIter.hasNext()) {
					FieldEntity field = fieldsIter.next();
					//					log.debug("field =" + field.getFieldName());
					//					log.debug(" value =" + rs.getObject(field.getFieldName()));
					Object obj = null;
					if (field.getFieldType().sqlType() == Types.CLOB)
						obj = rs.getClob(field.getFieldName());
					else
						obj = rs.getObject(field.getFieldName());

					if (obj instanceof Clob) {
						Clob clob = (Clob) obj;
						Reader reader = clob.getCharacterStream();
						String text = StringHelper.readText(reader);
						try {
							reader.close();
						} catch (Exception e) {
							e.printStackTrace();
							throw new SQLException("读取Clob数据出错：");
						}
						log.debug(" fieldName = " + field.getFieldName());
						log.debug(" text =" + text);
						row.addCell(field.getFieldName(), text);
					} else
						row.addCell(field.getFieldName(), obj);
					//					
					//					row.addCell(field.getFieldName(), rs.getObject(field
					//							.getFieldName()));
				}
				table.addRow(row);
				log.debug("end  add row");
			}
			log.debug(" end add table");
			return table;
		} finally {
			if (st != null)
				try {
					st.close();
				} catch (Exception e) {
					log.debug("数据连接池关闭st时发生错误：" + e.getMessage());
				}
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
					log.debug("数据连接池关闭rs时发生错误：" + e.getMessage());
				}
			if (dbSet != null)
				try {
					dbSet.close();
				} catch (Exception e) {
					log.debug("数据连接池关闭dbSet时发生错误：" + e.getMessage());
				}
			// if(connnection!=null)
			// connnection.close();
		}
	}

	private String analyzTableName(String querySql) {
		String lowerQuerySql = querySql.toLowerCase();
		//querySql = querySql.toLowerCase();
		String tableName;
		if (!StringUtils.contains(lowerQuerySql, "where"))
			tableName = StringUtils.substringAfter(lowerQuerySql, "from ");
		else
			tableName = StringUtils.substringBetween(lowerQuerySql, "from ",
					" where");

		if (StringUtils.contains(tableName, ",")
				|| StringUtils.contains(tableName, "join")) {
			tableName = EdiDb.TABLE_EDI_SYSTEMP;
		} else if (StringUtils.contains(tableName, " as ")) {
			tableName = StringUtils.substringBetween(lowerQuerySql, "from ",
					" as");
		}
		if (StringUtils.equalsIgnoreCase(tableName, EdiDb.TABLE_EDI_SYSTEMP))
			return tableName;
		else {
			int startLocation = StringUtils.indexOf(lowerQuerySql, tableName);
			int endLocation = startLocation + tableName.length();
			return StringUtils.substring(querySql, startLocation, endLocation);
		}
	}

	/**
	 * 执行无返回结果的语句，例如创建表，删除表，增删改数据和SQLDLL(数据库创建语言) 返回影响行数，dll时返回0
	 */
	public int excuteSql(String sql) throws SQLException {
		int returnVal = 0;
		Statement st = null;
		if (sql == null || sql.trim().equals("")) {
			return returnVal;
		}
		log.debug("excuteSql : " + sql);
		try {
			// connnection.setAutoCommit(false);
			st = connection.createStatement();
			returnVal = st.executeUpdate(sql);
			// connnection.commit();
		} finally {
			if (st != null)
				st.close();
			// if(connnection!=null)
			// connnection.close();
		}
		return returnVal;

	}

	public Boolean isOpen() {
		if (this.connection != null)
			try {
				return !this.connection.isClosed();
			} catch (SQLException e) {
				log.error("获取连接状态出现异常" + e.getMessage());
				e.printStackTrace();
			}
		return false;
	}

	/**
	 * 执行保存操作
	 * 
	 * @throws SQLException
	 */
	public int save(TableEntity tableEntity) throws SQLException {
		PreparedStatement st = null;
		try {
			List list = tableEntity.getFieldNameList();
			st = connection.prepareStatement(dialect.getSavePreparedString(
					list, tableEntity.getTableName()));
			// connnection.setAutoCommit(false);
			List rowList = tableEntity.getRows();
			Iterator rowIter = rowList.iterator();
			while (rowIter.hasNext()) {
				RowEntity row = (RowEntity) rowIter.next();
				Iterator it = list.iterator();
				while (it.hasNext()) {
					String fieldName = (String) it.next();
					//					log.debug("now finding filed : " + fieldName);
					ColumnCell columnCell = (ColumnCell) row.getCell(fieldName);
					//					log.debug("find field "
					//							+ (columnCell == null ? "false" : "true"));
					//					log.debug(columnCell);
					if (columnCell != null) {
						NullableType columnType = columnCell.getFieldType();
						log.debug("fieldName : " + fieldName);
						log.debug("fieldValue : " + columnCell.getFieldValue());
						log.debug("index : " + list.indexOf(fieldName));
						columnType.set(st, columnCell.getFieldValue(), list
								.indexOf(fieldName) + 1);
					}
					//					//当获取不到 字段值时 直接获取字段类别,添加 null
					//					else{
					//						NullableType columnType=tableEntity.getFieldType(fieldName);
					//						log.debug(" columnCell ==null columnType ="+columnType.getName() );
					//						columnType.set(st, null, list
					//								.indexOf(fieldName) + 1);
					//					}

				}
				st.addBatch();
			}
			st.executeBatch();
			// connnection.commit();
		} finally {
			// if(connnection!=null)
			// connnection.close();
			if (st != null)
				st.close();
		}
		return 0;
	}

	// /**
	// * 设置参数
	// */
	// public void setParameter(int index, Object value) {
	//
	// }

	/**
	 * 设置事物
	 */
	public void setTransaction() {
		try {
			connection.setAutoCommit(false);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void commit() throws SQLException {
		if (connection != null && connection.getAutoCommit() == false)
			connection.commit();
	}

	public void executePreparedEql(PreparedEql preparedEql) throws SQLException {
		PreparedStatement pst = null;
		String sql = dialect.translate(preparedEql.eql);

		try {

			pst = connection.prepareStatement(sql);
			for (PreparedParam param : preparedEql.getParams()) {
				NullableType type = param.fieldType;
				type.set(pst, param.fieldValue, param.index);
			}
			pst.execute();

		} finally {
			if (pst != null)
				pst.close();
		}
	}

	public Map<Integer, Object> executeProc(EdiCallableStatement proc)
			throws SQLException {
		CallableStatement _proc = null;
		String sql = proc.eql;
		log.debug("Proc sql : " + sql);

		try {
			_proc = connection.prepareCall(sql);
			for (PreparedParam param : proc.getParams()) {
				NullableType type = param.fieldType;
				type.set(_proc, param.fieldValue, param.index);
				log.debug("parmIndex :" + param.index + ";paramType : "
						+ param.fieldValue.getClass().getName()
						+ ";paramValue : " + param.fieldValue);
			}
			Map<Integer, Integer> registerParams = proc.getOutParams();
			for (Integer index : registerParams.keySet()) {
				_proc.registerOutParameter(index, registerParams.get(index));
			}
			_proc.execute();

			for (Integer index : registerParams.keySet()) {
				int sqlType = registerParams.get(index);
				switch (sqlType) {
				case Types.INTEGER:
					proc.getOutObjects().put(index, _proc.getInt(index));
					continue;
				case Types.VARCHAR:
					proc.getOutObjects().put(index, _proc.getString(index));
					continue;
				case Types.FLOAT:
					proc.getOutObjects().put(index, _proc.getFloat(index));
					continue;
				case Types.BOOLEAN:
					proc.getOutObjects().put(index, _proc.getBoolean(index));
					continue;
				case Types.DECIMAL:
					proc.getOutObjects().put(index, _proc.getBigDecimal(index));
					continue;
				default:
					proc.getOutObjects().put(index, _proc.getObject(index));
					break;
				}
			}
		} finally {
			if (_proc != null)
				_proc.close();
		}

		return proc.getOutObjects();
	}

	public TableEntity queryProc(EdiCallableStatement proc,
			Map<Integer, Object> outParamValues) throws SQLException {
		CallableStatement _proc = null;
		ResultSet rs = null;
		TableEntity table = new TableEntity("procedure");
		String sql = proc.eql;
		log.debug("Proc sql : " + sql);

		try {
			_proc = connection.prepareCall(sql);
			for (PreparedParam param : proc.getParams()) {
				NullableType type = param.fieldType;
				type.set(_proc, param.fieldValue, param.index);
				log.debug("parmIndex :" + param.index + ";paramType : "
						+ param.fieldValue.getClass().getName()
						+ ";paramValue : " + param.fieldValue);
			}
			Map<Integer, Integer> registerParams = proc.getOutParams();
			for (Integer index : registerParams.keySet()) {
				_proc.registerOutParameter(index, registerParams.get(index));
			}
			rs = _proc.executeQuery();
			for (Integer index : registerParams.keySet()) {
				int sqlType = registerParams.get(index);
				switch (sqlType) {
				case Types.INTEGER:
					proc.getOutObjects().put(index, _proc.getInt(index));
					continue;
				case Types.VARCHAR:
					proc.getOutObjects().put(index, _proc.getString(index));
					continue;
				case Types.FLOAT:
					proc.getOutObjects().put(index, _proc.getFloat(index));
					continue;
				case Types.BOOLEAN:
					proc.getOutObjects().put(index, _proc.getBoolean(index));
					continue;
				case Types.DECIMAL:
					proc.getOutObjects().put(index, _proc.getBigDecimal(index));
					continue;
				default:
					proc.getOutObjects().put(index, _proc.getObject(index));
					break;
				}
			}

			outParamValues = proc.getOutObjects();

			ResultSetMetaData meta = _proc.getMetaData();

			if (meta != null) {
				// 获取表中的字段信息
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					FieldEntity field = new FieldEntity();
					field.setFieldName(meta.getColumnName(i).toLowerCase());
					field.setFieldType(dialect.getSqlType(
							meta.getColumnType(i), meta.getPrecision(i), meta
									.getScale(i)));
					table.addField(field);
				}
				while (rs.next()) {
					Collection<FieldEntity> fields = table.getFieldEntitys()
							.values();
					Iterator<FieldEntity> fieldsIter = fields.iterator();
					RowEntity row = table.newRow();
					while (fieldsIter.hasNext()) {
						FieldEntity field = fieldsIter.next();
						Object obj = null;
						if (field.getFieldType().sqlType() == Types.CLOB)
							obj = rs.getClob(field.getFieldName());
						else
							obj = rs.getObject(field.getFieldName());

						if (obj instanceof Clob) {
							Clob clob = (Clob) obj;
							Reader reader = clob.getCharacterStream();
							String text = StringHelper.readText(reader);
							log.debug(" text =" + text);
							row.addCell(field.getFieldName(), text);
						} else
							row.addCell(field.getFieldName(), obj);
					}
					table.addRow(row);
				}
			}

		} finally {
			if (_proc != null)
				_proc.close();
		}

		return table;
	}

	public TableEntity queryProc(EdiCallableStatement proc) throws SQLException {
		CallableStatement _proc = null;
		ResultSet rs = null;
		TableEntity table = new TableEntity("procedure");
		String sql = proc.eql;
		log.debug("Proc sql : " + sql);

		try {
			_proc = connection.prepareCall(sql);
			for (PreparedParam param : proc.getParams()) {
				NullableType type = param.fieldType;
				type.set(_proc, param.fieldValue, param.index);
				log.debug("parmIndex :" + param.index + ";paramType : "
						+ param.fieldValue.getClass().getName()
						+ ";paramValue : " + param.fieldValue);
			}
			Map<Integer, Integer> registerParams = proc.getOutParams();
			for (Integer index : registerParams.keySet()) {
				_proc.registerOutParameter(index, registerParams.get(index));
			}
			rs = _proc.executeQuery();

			ResultSetMetaData meta = _proc.getMetaData();

			if (meta != null) {
				// 获取表中的字段信息
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					FieldEntity field = new FieldEntity();
					field.setFieldName(meta.getColumnName(i).toLowerCase());
					field.setFieldType(dialect.getSqlType(
							meta.getColumnType(i), meta.getPrecision(i), meta
									.getScale(i)));
					table.addField(field);
				}
				while (rs.next()) {
					Collection<FieldEntity> fields = table.getFieldEntitys()
							.values();
					Iterator<FieldEntity> fieldsIter = fields.iterator();
					RowEntity row = table.newRow();
					while (fieldsIter.hasNext()) {
						FieldEntity field = fieldsIter.next();
						Object obj = null;
						if (field.getFieldType().sqlType() == Types.CLOB)
							obj = rs.getClob(field.getFieldName());
						else
							obj = rs.getObject(field.getFieldName());

						if (obj instanceof Clob) {
							Clob clob = (Clob) obj;
							Reader reader = clob.getCharacterStream();
							String text = StringHelper.readText(reader);
							log.debug(" text =" + text);
							row.addCell(field.getFieldName(), text);
						} else
							row.addCell(field.getFieldName(), obj);
					}
					table.addRow(row);
				}
			}

		} finally {
			if (_proc != null)
				_proc.close();
		}

		return table;
	}

	public TableEntity queryPreparedEql(PreparedEql preparedEql)
			throws SQLException {
		PreparedStatement pst = null;
		log.debug("befor dialect eql : " + preparedEql);
		String sql = dialect.translate(preparedEql.eql);
		log.debug("after dialect eql : " + sql);
		String tableName = analyzTableName(sql);
		List<String> primFields = new ArrayList<String>();
		TableEntity table = new TableEntity(tableName);
		int start = 0;
		int pageSize = Integer.MAX_VALUE;
		boolean pagination = preparedEql.getCurrentPage() != null
				&& preparedEql.getPageSize() != null;

		if (pagination) {
			pageSize = preparedEql.getPageSize();
			start = pageSize * (preparedEql.getCurrentPage().intValue() - 1);

		}
		log.debug(" start= " + start);
		log.debug(" pageSize= " + pageSize);
		ResultSet rs = null;
		//		ResultSet dbSet = null;
		try {

			primFields = dialect.getPrimaryKeys(connection, tableName);

			//-------------一下这段进入方言处理---------
			//			DatabaseMetaData dbmeta = connection.getMetaData();
			//
			//			if (!tableName.equals(EdiDb.TABLE_EDI_SYSTEMP)) {
			//				// 获得主键信息
			//				dbSet = dbmeta.getPrimaryKeys(null, null, tableName);
			//				while (dbSet.next()) {
			//					primFields
			//							.add(dbSet.getString("COLUMN_NAME").toLowerCase());
			//				}
			//			}
			//--------------处理结束----------------
			//pst = connection.prepareStatement(sql);
			//TODO 设计2005的分页方式
			//			if (pagination){
			//				boolean hasOrderBy = StringUtils.contains(sql, " order by ");
			//				if (hasOrderBy){
			//					int orderbyPosition = StringUtils.indexOf(sql, " order by ");
			//					String afterOrderBy = StringUtils.substring(sql, orderbyPosition);
			//					for (String columnName : primFields){
			//						if (!StringUtils.contains(afterOrderBy, columnName)){
			//							StringUtils
			//						}
			//						
			//					}
			//				}
			//			}
			pst = connection.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			//			pst = connection.prepareStatement(sql);
			for (PreparedParam param : preparedEql.getParams()) {
				NullableType type = param.fieldType;
				type.set(pst, param.fieldValue, param.index);
			}
			if (pagination)
				pst.setMaxRows(start + pageSize);
			rs = pst.executeQuery();
			if (pagination)
				try {
					rs.absolute(start);
				} catch (Exception e) {
					e.printStackTrace();
					int index = 0;
					while (index < start) {
						if (rs.next())
							index++;
						else
							continue;
					}
				}
			ResultSetMetaData meta = pst.getMetaData();

			// 获取表中的字段信息
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				FieldEntity field = new FieldEntity();
				field.setFieldName(meta.getColumnName(i).toLowerCase());
				log.debug("-------------- fieldName : "
						+ meta.getColumnName(i)
						//+ ";fileScale : " + meta.getScale(i)
						//+ ";fieldPrecision : " + meta.getPrecision(i)
						+ ";fieldType : " + meta.getColumnType(i)
						+ ";fieldTypeName : " + meta.getColumnTypeName(i));
				field.setFieldType(dialect.getSqlType(meta.getColumnType(i),
						meta.getPrecision(i), meta.getScale(i)));
				//				log.debug("-------------fieldName : "+meta.getColumnName(i));
				//				log.debug("-------------fieldType : "+meta.getColumnType(i));

				if (primFields.contains(field.getFieldName())) {
					field.setIdentify(true);
				}
				table.addField(field);

			}
			// analyzeResultSet(table,connection,rs, sql);
			// 获取表中的行信息 

			while (rs.next()) {
				Collection<FieldEntity> fields = table.getFieldEntitys()
						.values();
				Iterator<FieldEntity> fieldsIter = fields.iterator();
				RowEntity row = table.newRow();
				while (fieldsIter.hasNext()) {
					FieldEntity field = fieldsIter.next();
					Object obj = null;
					if (field.getFieldType().sqlType() == Types.CLOB)
						obj = rs.getClob(field.getFieldName());
					else {
						if (field.getFieldType().sqlType() == Types.BIT)
							obj = rs.getBoolean(field.getFieldName());
						else
							obj = rs.getObject(field.getFieldName());
					}

					if (obj instanceof Clob) {
						Clob clob = (Clob) obj;
						Reader reader = clob.getCharacterStream();
						String text = StringHelper.readText(reader);
						try {
							reader.close();
						} catch (Exception e) {
							e.printStackTrace();
							throw new SQLException("读取Clob数据出错：");
						}
						log.debug(" text =" + text);
						row.addCell(field.getFieldName(), text);
					} else
						row.addCell(field.getFieldName(), obj);
				}
				table.addRow(row);
			}
		} finally {
			if (pst != null)
				pst.close();
			if (rs != null)
				rs.close();
			//			if (dbSet != null)
			//				dbSet.close();
		}

		return table;
	}

	/**
	 * 执行更新操作
	 * 
	 * @throws SQLException
	 */
	public int update(TableEntity tableEntity) throws SQLException {
		PreparedStatement st = null;
		Map<String, PreparedObject> preparedObjects = new HashMap<String, PreparedObject>();
		try {
			List list = tableEntity.getFieldNameList();
			List<String> keys = tableEntity.getPrimarykey();
			log.debug(" list " + list.size());

			st = connection.prepareStatement(getUpdatePreparedString(list,
					tableEntity, preparedObjects));
			// connnection.setAutoCommit(false);
			List rowList = tableEntity.getRows();
			Iterator rowIter = rowList.iterator();
			while (rowIter.hasNext()) {
				RowEntity rowEntity = (RowEntity) rowIter.next();
				Iterator it = list.iterator();
				while (it.hasNext()) {

					String fieldName = (String) it.next();

					if (!keys.contains(fieldName)) {
						ColumnCell columnCell = (ColumnCell) rowEntity
								.getColumnCells().get(fieldName);
						if (columnCell != null) {
							NullableType columnType = columnCell.getFieldType();
							log.debug(list.indexOf(fieldName) + 1);
							log.debug("fieldName =" + fieldName);
							log.debug("columnCell ="
									+ columnCell.getFieldValue());
							log.debug("fieldType = "
									+ columnType.getClass().getName());
							// columnType.set(st, columnCell.getFieldValue(),
							// list
							// .indexOf(fieldName) + 1);
							columnType.set(st, columnCell.getFieldValue(),
									preparedObjects.get(fieldName).index);
						}

					}
				}
				// 根据主键列增加条件
				List<String> keyList = tableEntity.getPrimarykey();
				Iterator<String> keyIt = keyList.iterator();
				while (keyIt.hasNext()) {
					String key = keyIt.next();
					log.debug("keyIt  " + key);
					ColumnCell columnCell = (ColumnCell) rowEntity
							.getColumnCells().get(key);
					NullableType columnType = columnCell.getFieldType();
					columnType.set(st, columnCell.getFieldValue(),
							preparedObjects.get(key).index);
				}
				st.addBatch();
			}
			st.executeBatch();
			// connnection.commit();
		} finally {
			// if(connnection!=null)
			// connnection.close();
			if (st != null)
				st.close();
		}
		return 0;
	}

	/**
	 * 开启事物
	 */
	public void beginTransaction() throws SQLException {
		if (this.connection != null && this.connection.isClosed() == false)
			this.connection.setAutoCommit(false);
	}

	/**
	 * 回滚事物
	 */
	public void rollbackTransaction() throws SQLException {
		if (this.connection != null && this.connection.isClosed() == false)
			this.connection.rollback();
	}

	private String getUpdatePreparedString(List fieldNameist,
			TableEntity tableEntity, Map<String, PreparedObject> preparedObjects) {
		if (tableEntity == null || tableEntity.getTableName() == null
				|| "".equals(tableEntity.getTableName()))
			throw new RuntimeException("表名为空！");
		StringBuffer sql = new StringBuffer();
		String tableName = tableEntity.getTableName();
		if (fieldNameist == null || fieldNameist.size() < 2)
			throw new RuntimeException("参数不足！");
		try {
			List<String> keys = tableEntity.getPrimarykey();

			sql.append("update ").append(tableName).append(" set ");
			Iterator it = fieldNameist.iterator();
			int count = 0;
			while (it.hasNext()) {
				String fieldName = (String) it.next();
				// 未到字段尾，并且包含主键
				if (keys.contains(fieldName) && it.hasNext()) {
					continue;
				}

				// 到了字段尾，同时包含主键名
				if (keys.contains(fieldName) && !it.hasNext()) {
					sql.append(" where ");
					Iterator<String> keyIt = keys.iterator();
					while (keyIt.hasNext()) {
						String key = keyIt.next();
						count++;
						PreparedObject preO = new PreparedObject(count,
								fieldName);
						preparedObjects.put(fieldName, preO);
						sql.append(key).append(" = ? ");
						if (keyIt.hasNext())
							sql.append(" and ");
					}
				}

				// 到了字段尾，同时不包含主键名
				if (!keys.contains(fieldName) && !it.hasNext()) {
					sql.append(",").append(fieldName).append(" = ? ");
					count++;
					PreparedObject preO = new PreparedObject(count, fieldName);
					preparedObjects.put(fieldName, preO);
					sql.append(" where ");
					Iterator<String> keyIt = keys.iterator();
					while (keyIt.hasNext()) {
						String key = keyIt.next();
						sql.append(key).append(" = ? ");
						count++;
						PreparedObject preO1 = new PreparedObject(count, key);
						preparedObjects.put(key, preO1);
						if (keyIt.hasNext())
							sql.append(" and ");
					}
				}

				// 不包含主键,未到字段尾
				if (!keys.contains(fieldName) && it.hasNext()) {
					if (count == 0) {
						sql.append(fieldName).append(" = ? ");
					} else {
						sql.append(",").append(fieldName).append(" = ? ");
					}
					count++;
					PreparedObject preO = new PreparedObject(count, fieldName);
					preparedObjects.put(fieldName, preO);
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		log.debug(" return sql =" + sql);
		return sql.toString();
	}

	private class PreparedObject {
		public int index;
		public String fieldName;

		public PreparedObject(int index, String fieldName) {
			this.index = index;
			this.fieldName = fieldName;
		}
	}

	public Connection getConnection() {
		return this.connection;
	}

}
